#!/usr/bin/env python
# -*- coding: utf-8 -*-

from utils import log

from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, Float, Boolean, String, Unicode, UnicodeText, \
    DateTime, desc
from data_pool_mysql import engine

metadata = MetaData()
Session = sessionmaker()
Session.configure(bind=engine)

"""journey_info_item collection has
    userid:         用户id（int）
    journeyid:         游记id（非全局）（int）
    sightid:         景点id（int）
    ordernum:          景点游览顺序（int）
    markid:         景点评论id（datetime）
"""

journey_info_table_dict = {}


class JourneyInfoTables(object):
    def make_table(self, p_cityid):
        if journey_info_table_dict.has_key(p_cityid):
            return journey_info_table_dict[p_cityid]

        class JourneyInfoItem(object):
            def __init__(self, p_cityid):
                self.p_cityid = p_cityid

            def to_dict(self):
                res = {}
                res['userid'] = self.userid
                res['journeyid'] = self.journeyid
                res['sightid'] = self.sightid
                res['ordernum'] = self.ordernum
                res['markid'] = self.markid
                return res

            def __repr__(self):
                return "%d" % (self.userid)

        metadata = MetaData()
        journey_info_item_table = Table(
            'journey_info_' + str(p_cityid), metadata,
            Column('id', Integer, primary_key=True),
            Column('userid', Integer, index=True, nullable=False),
            Column('journeyid', Integer, index=True, nullable=False),
            Column('sightid', Integer, index=True, nullable=False),
            Column('ordernum', Integer, nullable=False),
            Column('markid', Integer, nullable=False))
        mapper(JourneyInfoItem, journey_info_item_table)
        table = {'metadata': metadata,
                 'table': journey_info_item_table,
                 'class': JourneyInfoItem}
        journey_info_table_dict[p_cityid] = table
        return table


def create_journey_info_item_table_if_not_exist(p_cityid):
    table = JourneyInfoTables().make_table(p_cityid)
    table['metadata'].create_all(engine)


def add_journey_info_item(p_cityid, p_item):
    table = JourneyInfoTables().make_table(p_cityid)
    item = table['class'](p_cityid)
    item.userid = p_item['userid']
    item.journeyid = p_item['journeyid']
    item.sightid = p_item['sightid']
    item.ordernum = p_item['ordernum']
    item.markid = p_item['markid']
    try:
        session = Session()
        session.add(item)
        session.flush()
        session.commit()
        return True
    except Exception, e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def get_journey_info_all_by_userid(p_cityid, p_userid):
    try:
        session = Session()
        table = JourneyInfoTables().make_table(p_cityid)
        items = session.query(table['class']).filter_by(userid=p_userid).order_by(table['class'].ordernum.asc()).all()

        res = {}
        for item in items:
            res[item.markid] = item.to_dict()
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()
        # session.remove()

# cityid, sightid, authorid
'''
 [{'ordernum': 1L, 'journeyid': 1L, 'sightid': 1L, 'userid': 111L, 'markid': 1L}]
'''


def get_journey_info_except_users(p_cityid, p_sightid, p_userids, start=0, count=10):
    try:
        session = Session()
        table = JourneyInfoTables().make_table(p_cityid)
        items = session.query(table['class']).filter(table['class'].sightid == p_sightid).filter(
            ~table['class'].userid.in_(p_userids)).order_by(desc(table['class'].id)).offset(start).limit(count)

        res = []
        for item in items:
            res.append(item.to_dict())
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()
        # session.remove()


def get_journey_info_all_by_cityid_userid_journeyid(p_cityid, p_userid, p_journeyid):
    try:
        session = Session()
        table = JourneyInfoTables().make_table(p_cityid)
        items = session.query(table['class']).filter_by(userid=p_userid, journeyid=p_journeyid).all()
        res = {}
        for item in items:
            res[item.markid] = item.to_dict()
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()


def delete_journey_info_by_userid(p_cityid, p_userid):
    try:
        session = Session()
        table = JourneyInfoTables().make_table(p_cityid)
        item = session.query(table['class']).filter_by(userid=p_userid).delete()
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


if __name__ == "__main__":
    '''
    test_city = 1
    create_journey_info_item_table_if_not_exist(test_city)
    test_item = {'userid': 1,
                 'journeyid': 1,
                 'sightid': 5,
                 'ordernum': 5,
                 'markid': 5 }
    add_journey_info_item(test_city, test_item)
    print get_journey_info_all_by_cityid_userid_journeyid( 1, 112, 1 )
    '''
    # print get_journey_info_all_by_userid(1, 113)
    print get_journey_info_except_users(1, 1, [12345, 789])
